package com.glorystone.ins.service.impl;

import com.glorystone.ins.common.common.PageVo;
import com.glorystone.ins.common.util.ReflectionUtils;
import com.glorystone.ins.common.util.StringUtil;
import com.glorystone.ins.dao.CapitalRecordMapper;
import com.glorystone.ins.dao.OrderMapper;
import com.glorystone.ins.dao.ReceivableMapper;
import com.glorystone.ins.domain.*;
import com.glorystone.ins.enums.CapitalRecordStatusEnum;
import com.glorystone.ins.enums.MainOrderStatusEnum;
import com.glorystone.ins.enums.OrderStatusEnum;
import com.glorystone.ins.interceptor.PageList;
import com.glorystone.ins.service.BusinessLogService;
import com.glorystone.ins.service.CreditTotalService;
import com.glorystone.ins.service.IReceivableService;
import com.glorystone.ins.vo.*;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.poi.xssf.usermodel.*;
import org.apache.shiro.SecurityUtils;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;

@Service
public class ReceivableServiceImpl implements IReceivableService {

    @Autowired
    private ReceivableMapper receivableMapper;

    @Autowired
    private CapitalRecordMapper capitalRecordMapper;

    @Autowired
    private SqlSessionTemplate sqlSessionTemplate; //从spring注入原有的sqlSessionTemplate

    @Autowired
    private CreditTotalService creditTotalService;

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private BusinessLogService businessLogService;

    @Override
    public PageList<ReceivableVO> findReceivableList(PageVo vo) {
        PageList<ReceivableVO> recordList = receivableMapper.findReceivableList(vo, vo.getPageable());
        return recordList;
    }

    @Override
    public PageList<CheckDataVO> findCheckDataList(PageVo vo) {
        PageList<CheckDataVO> checkDataList = receivableMapper.findCheckDataList(vo, vo.getPageable());
        return checkDataList;
    }

    /**
     * 批量核对
     * @param myFile
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> importCheck(MultipartFile myFile, long userId) {
        ImportDataVO iData = new ImportDataVO();
        List list = null;
        Map map = new HashMap();
        try {
            Map resultMap = readXlsx(myFile.getInputStream(), iData);
            if (resultMap.get("status").equals("0001")) {
                list = (List) resultMap.get("result");
                List<CheckDataVO> checkDataList = new ArrayList<CheckDataVO>();
                List<ReceivableVO> receivableList = receivableMapper.queryReceivableListForCheck();
                int checkResult = 1;
                int matchingFlag = 0; //匹配标识
                int matchingCount = 0; //匹配的条数
                int noMatchingCount = 0; //未匹配的条数
                for (int j = 0; j < list.size(); j++) {
                    matchingFlag = 0;
                    ImportDataVO importData = (ImportDataVO) list.get(j);
                    for (int i = 0; i < receivableList.size(); i++) {
                        ReceivableVO receivable = receivableList.get(i);
                        if (importData.getOrderNum().equals(receivable.getOrderNum())) {
                            matchingFlag = 1;
                            CheckDataVO checkData = new CheckDataVO();
                            BigDecimal signPreminm = new BigDecimal(importData.getSignPreminm()); //导入的签单保费即税后保费
                            BigDecimal texPremium = receivable.getTexPremium(); //系统中存储的税后保费
                            //计算误差值是否为1元（含）
                            int errorValue = signPreminm.subtract(texPremium).abs().compareTo(new BigDecimal("1"));
                            //核对税后金额，误差为1元（含）
                            if (errorValue == -1 || errorValue == 0) {
                                checkData.setCheckSignPreminmResult(1);
                                checkData.setSignPreminm(receivable.getTexPremium());
                                checkData.setCheckSignPreminm(new BigDecimal(importData.getSignPreminm()));
                            } else {
                                checkResult = 2;
                                checkData.setCheckSignPreminmResult(0);
                                checkData.setSignPreminm(receivable.getTexPremium());
                                checkData.setCheckSignPreminm(new BigDecimal(importData.getSignPreminm()));
                            }
                            //核对跟单点位
                            BigDecimal followPoint = getPercent(importData.getFollowPoint());
                            if (followPoint.compareTo(receivable.getFollowPoint()) == 0) {
                                checkData.setChechFollowResult(1);
                                checkData.setFollowRatio(receivable.getFollowPoint());
                                checkData.setCheckFollowRatio(followPoint);
                            } else {
                                checkResult = 2;
                                checkData.setChechFollowResult(0);
                                checkData.setFollowRatio(receivable.getFollowPoint());
                                checkData.setCheckFollowRatio(followPoint);
                            }
                            //核对补点点位
                            BigDecimal replenishPoint = getPercent(importData.getReplenishPoint());
                            if (replenishPoint.compareTo(receivable.getReplenishPoint()) == 0) {
                                checkData.setCheckReplenishResult(1);
                                checkData.setReplenishRatio(receivable.getReplenishPoint());
                                checkData.setCheckReplenishRatio(replenishPoint);
                            } else {
                                checkResult = 2;
                                checkData.setCheckReplenishResult(0);
                                checkData.setReplenishRatio(receivable.getReplenishPoint());
                                checkData.setCheckReplenishRatio(replenishPoint);
                            }
                            checkData.setUserId(userId);
                            checkData.setCapitalId(receivable.getTid());
                            checkData.setCheckResult(checkResult);
                            checkData.setOrderNum(receivable.getOrderNum());
                            checkData.setPaySubject(receivable.getPaySubject());
                            checkData.setInsurer(receivable.getInsurer());
                            checkData.setInsuranceType(receivable.getInsuranceType());
                            checkData.setRealPremium(receivable.getRealPremium());
                            checkData.setFollowPoundage(receivable.getFollowRpoundage());
                            checkData.setReplenishPoundage(receivable.getReplenishRpoundage());
                            checkData.setIsTicketOpen(0);
                            checkData.setStatus(CapitalRecordStatusEnum.TO_MAKE_OUT_INVOICE.getValue());
                            checkData.setCheckDate(LocalDate.now());
                            checkData.setUpdateBy(userId);
                            checkData.setUpdateDate(LocalDate.now());
                            checkDataList.add(checkData);
                        }
                    }
                    if (matchingFlag == 1) {
                        matchingCount ++; //累计匹配的条数
                    } else {
                        noMatchingCount ++; //累计未匹配的条数
                    }
                }
                if (checkDataList.size() > 0) {
                    //插入前删除该用户之前的核对数据
                    int num = receivableMapper.deleteCheckData(userId);
                    //批量插入核对结果数据
                    String batchResult = doBatchSql(checkDataList, "insert", "batchInsertCheckData");
                    if ("0001".equals(batchResult)) {
                        map.put("status", "0001");
                        map.put("message", "数据核对成功");
                        map.put("matchingCount", matchingCount);
                        map.put("noMatchingCount", noMatchingCount);
                    } else {
                        map.put("status", "0002");
                        map.put("message", "批量执行发生错误");
                    }
                } else {
                    map.put("status", "0003");
                    map.put("message", "未找到匹配结果，请确认上传内容是否正确");
                }
            } else if (resultMap.get("status").equals("0002")) {
                map.put("status", "0005");
                map.put("message", "导入的模板有误，请确认上传模板是否正确！");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message", "数据核对失败");
        }
        return map;
    }

    /**
     * 批量核对确认
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> confirmCheck(long userId) {
        Map map = new HashMap();
        try {
            //获取准备更新的核对数据
            List<CheckDataVO> checkDataList = receivableMapper.selectCheckDataListByUserId(userId);
            //批量更新核对结果数据到应收订单
            String batchResult = doBatchSql(checkDataList, "update", "batchUpdateReceivable");
            if ("0001".equals(batchResult)) {
                batchInsertBusinessLog(checkDataList, BusinessLog.BusinessTypeEnum.ORDER.getValue(), BusinessLog.ContentEnum.RECEIVABLE_CHECK.getValue(), BusinessLog.ContentEnum.RECEIVABLE_CHECK.getValue());
                map.put("status", "0001");
                map.put("message","数据核对成功");
            } else {
                map.put("status", "0002");
                map.put("message","批量执行发生错误");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message","数据核对失败");
        }
        return map;
    }

    /**
     * 批量开票
     * @param myFile
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> importInvoice(MultipartFile myFile, long userId) {
        ImportDataVO iData = new ImportDataVO();
        List list = null;
        Map map = new HashMap();
        try {
            Map resultMap = readXlsx(myFile.getInputStream(), iData);
            if (resultMap.get("status").equals("0001")) {
                list = (List) resultMap.get("result");
                List<CheckDataVO> checkDataList = new ArrayList<CheckDataVO>();
                List<ReceivableVO> receivableList = receivableMapper.queryReceivableListForInvoice();
                int matchingFlag = 0; //匹配标识
                int matchingCount = 0; //匹配的条数
                int noMatchingCount = 0; //未匹配的条数
                BigDecimal invoiceAmount = new BigDecimal("0");
                for (int j = 0; j < list.size(); j++) {
                    matchingFlag = 0;
                    ImportDataVO importData = (ImportDataVO) list.get(j);
                    for (int i = 0; i < receivableList.size(); i++) {
                        ReceivableVO receivable = receivableList.get(i);
                        if (importData.getOrderNum().equals(receivable.getOrderNum())) {
                            matchingFlag = 1;
                            CheckDataVO checkData = new CheckDataVO();
                            BigDecimal amount = new BigDecimal(importData.getFollowRpoundage()).add(new BigDecimal(importData.getReplenishRpoundage()));
                            invoiceAmount = invoiceAmount.add(amount);

                            checkData.setUserId(userId);
                            checkData.setCapitalId(receivable.getTid());
                            checkData.setOrderNum(receivable.getOrderNum());
                            checkData.setPaySubject(receivable.getPaySubject());
                            checkData.setInsurer(receivable.getInsurer());
                            checkData.setInsuranceType(receivable.getInsuranceType());
                            checkData.setRealPremium(receivable.getRealPremium());
                            checkData.setSignPreminm(receivable.getTexPremium());
                            checkData.setCheckSignPreminm(receivable.getCheckPremium());
                            checkData.setFollowRatio(receivable.getFollowPoint());
                            checkData.setReplenishRatio(receivable.getReplenishPoint());
                            checkData.setCheckFollowRatio(receivable.getCheckFollowPoint());
                            checkData.setCheckReplenishRatio(receivable.getCheckReplenishPoint());
                            checkData.setFollowPoundage(new BigDecimal(importData.getFollowRpoundage()));
                            checkData.setReplenishPoundage(new BigDecimal(importData.getReplenishRpoundage()));
                            checkData.setCheckResult(receivable.getCheckResult());
                            checkData.setChechFollowResult(Integer.parseInt(receivable.getCheckFollowResult()));
                            checkData.setCheckReplenishResult(Integer.parseInt(receivable.getCheckReplenishResult()));
                            checkData.setCheckSignPreminmResult(Integer.parseInt(receivable.getCheckPremiumResult()));
                            checkData.setInvoiceAmount(amount);
                            checkData.setInvoiceDate(LocalDate.now());
                            checkData.setIsTicketOpen(0);
                            checkData.setStatus(CapitalRecordStatusEnum.FOR_COLLECTION.getValue());
                            checkData.setUpdateBy(userId);
                            checkData.setUpdateDate(LocalDate.now());

                            checkDataList.add(checkData);
                        }
                    }
                    if (matchingFlag == 1) {
                        matchingCount ++; //累计匹配的条数
                    } else {
                        noMatchingCount ++; //累计未匹配的条数
                    }
                }
                if (checkDataList.size() > 0) {
                    //插入前删除该用户之前的核对数据
                    int num = receivableMapper.deleteCheckData(userId);
                    //批量插入核对结果数据
                    String batchResult = doBatchSql(checkDataList, "insert", "batchInsertCheckData");
                    if ("0001".equals(batchResult)) {
                        map.put("status", "0001");
                        map.put("message", "批量开票成功");
                        map.put("invoiceCount", checkDataList.size());
                        map.put("invoiceAmount", invoiceAmount);
                        map.put("matchingCount", matchingCount);
                        map.put("noMatchingCount", noMatchingCount);
                    } else {
                        map.put("status", "0002");
                        map.put("message", "批量执行发生错误");
                    }
                } else {
                    map.put("status", "0003");
                    map.put("message", "未找到匹配结果，请确认上传内容是否正确");
                }
            } else if (resultMap.get("status").equals("0002")) {
                map.put("status", "0005");
                map.put("message", "导入的模板有误，请确认上传模板是否正确！");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message","批量开票失败");
        }
        return map;
    }

    /**
     * 批量开票确认
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> confirmInvoice(long userId) {
        Map map = new HashMap();
        try {
            //获取准备更新的核对数据
            List<CheckDataVO> checkDataList = receivableMapper.selectCheckDataListByUserId(userId);
            //批量更新核对结果数据到应收订单
            String batchResult = doBatchSql(checkDataList, "update", "batchUpdateReceivableInvoice");
            if ("0001".equals(batchResult)) {
                batchInsertBusinessLog(checkDataList, BusinessLog.BusinessTypeEnum.ORDER.getValue(), BusinessLog.ContentEnum.RECEIVABLE_INVOICE.getValue(), BusinessLog.ContentEnum.RECEIVABLE_INVOICE.getValue());
                map.put("status", "0001");
                map.put("message","批量开票成功");
            } else {
                map.put("status", "0002");
                map.put("message","批量执行发生错误");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message","批量开票失败");
        }
        return map;
    }

    /**
     * 批量收款
     * @param myFile
     * @param gatheringDate
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> importGathering(MultipartFile myFile, String gatheringDate, long userId) {
        ImportDataVO iData = new ImportDataVO();
        List list = null;
        Map map = new HashMap();
        try {
            Map resultMap = readXlsx(myFile.getInputStream(), iData);
            if (resultMap.get("status").equals("0001")) {
                list = (List) resultMap.get("result");
                List<CheckDataVO> checkDataList = new ArrayList<CheckDataVO>();
                List<ReceivableVO> receivableList = receivableMapper.queryReceivableListForGathering();
                BigDecimal gatheringAmount = new BigDecimal("0"); //待收款总金额
                for (int i = 0; i < receivableList.size(); i++) {
                    ReceivableVO receivable = receivableList.get(i);
                    for (int j = 0; j < list.size(); j++) {
                        ImportDataVO importData = (ImportDataVO) list.get(j);
                        if (importData.getOrderNum().equals(receivable.getOrderNum())) {
                            CheckDataVO checkData = new CheckDataVO();
                            //系统中存储的应收返点 = 跟单手续费 + 补点手续费
                            BigDecimal mustPremiumAmt = receivable.getFollowRpoundage().add(receivable.getReplenishRpoundage());
                            //导入的实收返点 = 跟单手续费 + 补点手续费
                            BigDecimal realPremiumAmt = new BigDecimal(importData.getFollowRpoundage()).add(new BigDecimal(importData.getReplenishRpoundage()));
                            //计算误差值是否为1元（含）
                            int errorValue = mustPremiumAmt.subtract(realPremiumAmt).abs().compareTo(new BigDecimal("1"));
                            //核对税后金额，误差在1元以内
                            if (errorValue == -1 || errorValue == 0) {
                                checkData.setStatus(CapitalRecordStatusEnum.SETTLED.getValue());
                            } else {
                                checkData.setStatus(CapitalRecordStatusEnum.TO_AUDIT.getValue());
                            }
                            //累计实收返点
                            gatheringAmount = gatheringAmount.add(realPremiumAmt);

                            checkData.setUserId(userId);
                            checkData.setCapitalId(receivable.getTid());
                            checkData.setOrderNum(receivable.getOrderNum());
                            checkData.setPaySubject(receivable.getPaySubject());
                            checkData.setInsurer(receivable.getInsurer());
                            checkData.setInsuranceType(receivable.getInsuranceType());
                            checkData.setRealPremium(receivable.getRealPremium());
                            checkData.setSignPreminm(receivable.getTexPremium());
                            checkData.setCheckSignPreminm(receivable.getCheckPremium());
                            checkData.setFollowRatio(receivable.getFollowPoint());
                            checkData.setReplenishRatio(receivable.getReplenishPoint());
                            checkData.setCheckFollowRatio(receivable.getCheckFollowPoint());
                            checkData.setCheckReplenishRatio(receivable.getCheckReplenishPoint());
                            //checkData.setFollowPoundage(receivable.getFollowRpoundage());
                            //checkData.setReplenishPoundage(receivable.getReplenishRpoundage());
                            checkData.setFollowPoundage(new BigDecimal(importData.getFollowRpoundage()));
                            checkData.setReplenishPoundage(new BigDecimal(importData.getReplenishRpoundage()));
                            checkData.setCheckResult(receivable.getCheckResult());
                            checkData.setChechFollowResult(Integer.parseInt(receivable.getCheckFollowResult()));
                            checkData.setCheckReplenishResult(Integer.parseInt(receivable.getCheckReplenishResult()));
                            checkData.setCheckSignPreminmResult(Integer.parseInt(receivable.getCheckPremiumResult()));
                            checkData.setInvoiceAmount(receivable.getInvoiceValue());
                            checkData.setInvoiceDate(receivable.getTicketOpenTime());
                            checkData.setReceiptDate(LocalDate.now());
                            checkData.setRealPremiumAmt(realPremiumAmt);
                            checkData.setIsTicketOpen(1);
                            checkData.setUpdateBy(userId);
                            checkData.setUpdateDate(LocalDate.now());

                            checkDataList.add(checkData);
                        }
                    }
                }
                if (checkDataList.size() > 0) {
                    //插入前删除该用户之前的核对数据
                    int num = receivableMapper.deleteCheckData(userId);
                    //批量插入核对结果数据
                    String batchResult = doBatchSql(checkDataList, "insert", "batchInsertCheckData");
                    if ("0001".equals(batchResult)) {
                        map.put("status", "0001");
                        map.put("message", "批量收款成功");
                        map.put("gatheringCount", checkDataList.size());
                        map.put("gatheringAmount", gatheringAmount);
                    } else {
                        map.put("status", "0002");
                        map.put("message", "批量执行发生错误");
                    }
                } else {
                    map.put("status", "0003");
                    map.put("message", "未找到匹配结果，请确认上传内容是否正确");
                }
            } else if (resultMap.get("status").equals("0002")) {
                map.put("status", "0005");
                map.put("message", "导入的模板有误，请确认上传模板是否正确！");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message","批量收款失败");
        }
        return map;
    }

    /**
     * 批量收款确认
     * @param userId
     * @return
     */
    @Override
    public Map<?, ?> confirmGathering(long userId) {
        Map map = new HashMap();
        try {
            //获取准备更新的核对数据
            List<CheckDataVO> checkDataList = receivableMapper.selectCheckDataListByUserId(userId);
            //批量更新核对结果数据到应收订单
            String batchResult = doBatchSql(checkDataList, "update", "batchUpdateReceivableGathering");
            if ("0001".equals(batchResult)) {
                batchInsertBusinessLog(checkDataList, BusinessLog.BusinessTypeEnum.ORDER.getValue(), BusinessLog.ContentEnum.RECEIVABLE_GATHERING.getValue(), BusinessLog.ContentEnum.RECEIVABLE_GATHERING.getValue());
                map.put("status", "0001");
                map.put("message","批量收款成功");
            } else {
                map.put("status", "0002");
                map.put("message","批量执行发生错误");
            }
        } catch (Exception e) {
            map.put("status", "0004");
            map.put("message","批量收款失败");
        }
        return map;
    }

    /**
     * 执行数据库批量操作
     * @param doList
     * @return
     * @throws Exception
     */
    public String doBatchSql(List<?> doList, String doType, String doMapperMethod) throws Exception {
        String result = null;
        SqlSession batchSqlSession = null;
        try {
            // 获取批量方式的sqlsession
            batchSqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
            int batchCount = 1000;// 每批commit的个数
            int batchLastIndex = batchCount;// 每批最后一个的下标
            for (int index = 0; index < doList.size();) {
                if (batchLastIndex >= doList.size()) {
                    batchLastIndex = doList.size();
                    if ("insert".equals(doType)) {
                        batchSqlSession.insert(doMapperMethod, doList.subList(index, batchLastIndex));
                    } else if ("update".equals(doType)) {
                        batchSqlSession.update(doMapperMethod, doList.subList(index, batchLastIndex));
                    }
                    //手动每1000个一提交，提交后无法回滚
                    batchSqlSession.commit();
                    //清理缓存，防止溢出
                    batchSqlSession.clearCache();
                    System.out.println("index:" + index + " batchLastIndex:" + batchLastIndex);
                    result = "0001"; //执行成功
                    break; //数据插入完毕，退出循环
                } else {
                    if ("insert".equals(doType)) {
                        batchSqlSession.insert(doMapperMethod, doList.subList(index, batchLastIndex));
                    } else if ("update".equals(doType)) {
                        batchSqlSession.update(doMapperMethod, doList.subList(index, batchLastIndex));
                    }
                    //手动每1000个一提交，提交后无法回滚
                    batchSqlSession.commit();
                    //清理缓存，防止溢出
                    batchSqlSession.clearCache();
                    System.out.println("index:" + index + " batchLastIndex:" + batchLastIndex);
                    index = batchLastIndex; //设置下一批下标
                    batchLastIndex = index + (batchCount - 1);
                    result = "0001"; //执行成功
                }
            }
        } catch (Exception e) {
            //没有提交的数据可以回滚
            batchSqlSession.rollback();
            result = "0002"; //批量执行发生错误
        } finally {
            batchSqlSession.close();
        }
        return result;
    }

    /**
     * 读取xlsx文件内容
     *
     * @return List<XlsDto>对象
     * @throws IOException 输入/输出(i/o)异常
     */
    @SuppressWarnings("resource")
    public static Map readXlsx(InputStream is, Object obj) throws Exception {
        Map map = new HashMap();
        List<Object> list = new ArrayList<Object>();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        //校验导入模板
        boolean bl = checkImportTemplet(xssfWorkbook);
        if (bl) {
            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
                XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
                if (xssfSheet == null) {
                    continue;
                }
                // 循环行Row
                for (int rowNum = 2; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    String xh1 = getValueXlsx(xssfRow.getCell(1));
                    if (xssfRow == null || !StringUtil.checkNotEmpty(xh1)) {
                        break;
                    }
                    List<String> fieldsType = ReflectionUtils.getFieldType(obj);
                    Field[] fields = ReflectionUtils.getFields(obj);
                    Field field = null;
                    String fieldType = null;
                    obj = obj.getClass().newInstance();
                    for (int j = 2; j < fieldsType.size(); j++) {
                        XSSFCell xh = xssfRow.getCell(j - 1);
                        if (xh == null) {
                            continue;
                        }
                        field = fields[j];
                        fieldType = fieldsType.get(j);
                        String fieldSetName = ReflectionUtils.setMethod(field.getName());
                        Method fieldSetMet = obj.getClass().getMethod(fieldSetName, field.getType());
                        setReadValueXlsx(fieldSetMet, fieldType, obj, xh);
                    }
                    list.add(obj);
                }
            }
            map.put("status", "0001");
            map.put("result", list);
        } else {
            map.put("status", "0002");
            map.put("result", "模板错误");
        }
        return map;
    }

    /**
     * 得到Excel表中的值
     * @param xssfCell Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    @SuppressWarnings({ "static-access", "deprecation" })
    private static String getValueXlsx(XSSFCell xssfCell) {
        if (xssfCell.getCellType() == xssfCell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(xssfCell.getBooleanCellValue());
        } else if (xssfCell.getCellType() == xssfCell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(xssfCell.getNumericCellValue());
        } else {
            // 返回字符串类型的值
            return String.valueOf(xssfCell.getStringCellValue());
        }
    }

    public static void setReadValueXlsx(Method fieldSetMet, String type, Object obj, XSSFCell sx) throws Exception {
        if (type == null || "".equals(type)) {
            return;
        }
        if (type.equals("class java.lang.String")) {
            if (getValueXlsx(sx) == null || "".equals(getValueXlsx(sx))) {
                return;
            }
            fieldSetMet.invoke(obj, getValueXlsx(sx));
        }
        if (type.equals("class java.lang.Integer")) {
            fieldSetMet.invoke(obj, Integer.parseInt(getValueXlsx(sx)));
        }
        if (type.equals("class java.lang.Boolean")) {
            fieldSetMet.invoke(obj, Boolean.parseBoolean(getValueXlsx(sx)));
        }
        if (type.equals("int")) {
            fieldSetMet.invoke(obj, (int)Float.parseFloat(getValueXlsx(sx)));
        }
//        if (type.equals("class java.util.Date")) {
//            field.set(obj, (Date) getValue(sx));
//        }
        if (type.equals("float")) {
            fieldSetMet.invoke(obj, Float.parseFloat(getValueXlsx(sx)));
        }
        // 如果类型是Double
        if (type.equals("class java.lang.Double")) {
            fieldSetMet.invoke(obj, Double.parseDouble(getValueXlsx(sx)));
        }
        if (type.equals("boolean")) {
            fieldSetMet.invoke(obj, Boolean.parseBoolean(getValueXlsx(sx)));
        }
        // 如果类型是Short
        if (type.equals("class java.lang.Short")) {
            fieldSetMet.invoke(obj, Short.parseShort(getValueXlsx(sx)));
        }
    }

    //格式化百分比字段值
    @Override
    public BigDecimal getPercent(String percent) {
        BigDecimal result = new BigDecimal("0");
        if (percent.contains("%")) {
            String percentStr = percent.split("%")[0];
            result = new BigDecimal(percentStr);
        } else {
            result = new BigDecimal(percent).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
        }
        return result;
    }

    /**
     * 应收订单单笔核对
     * @param receivable
     * @return
     */
    @Override
    public Map<?,?> doOrderCheck(ReceivableVO receivable) {
        ReceivableVO receivableVO = receivableMapper.queryReceivableForCheck(receivable.getTid());

        if (receivable.getCheckResult() == 2) {
            BigDecimal checkTexPremium = receivable.getTexPremium(); //页面输入的核对税后保费
            BigDecimal texPremium = receivableVO.getTexPremium(); //系统中存储的税后保费
            //计算误差值是否为1元（含）
            int errorTexPremium = checkTexPremium.subtract(texPremium).abs().compareTo(new BigDecimal("1"));
            //核对税后金额，误差为1元（含）
            if (errorTexPremium == -1 || errorTexPremium == 0) {
                receivable.setCheckPremiumResult("1");
            } else {
                receivable.setCheckPremiumResult("0");
            }
            //页面输入的核对跟单点位
            BigDecimal checkFollowPoint = receivable.getFollowPoint();
            BigDecimal followPoint = receivableVO.getFollowPoint(); //系统中存储的跟单点位
            if (followPoint.compareTo(checkFollowPoint) == 0) {
                receivable.setCheckFollowResult("1");
            } else {
                receivable.setCheckFollowResult("0");
            }
            //页面输入的核对补点点位
            BigDecimal checkReplenishPoint = receivable.getReplenishPoint();
            BigDecimal replenishPoint = receivableVO.getReplenishPoint(); //系统中存储的补点点位
            if (replenishPoint.compareTo(checkReplenishPoint) == 0) {
                receivable.setCheckReplenishResult("1");
            } else {
                receivable.setCheckReplenishResult("0");
            }
        } else if (receivable.getCheckResult() == 1) {
            receivable.setCheckPremiumResult("1");
            receivable.setCheckFollowResult("1");
            receivable.setCheckReplenishResult("1");
        }
        Map map = updateForOrderCheck(receivable, BusinessLog.ContentEnum.RECEIVABLE_CHECK.getValue(), receivable.getRemark());
        return map;
    }

    @Override
    public Map<?, ?> updateForOrderCheck(ReceivableVO receivable, String businessContent, String remark) {
        Map map = new HashMap();
        try {
            //更新核对数据
            int num = receivableMapper.updateForOrderCheck(receivable);
            if (num > 0) {
                batchInsertBusinessLog(receivable.getTid(), businessContent, remark);
            }
            map.put("status", "0001");
            map.put("message","执行成功");
        } catch (Exception e) {
            map.put("status", "0002");
            map.put("message","执行失败");
        }
        return map;
    }

    @Override
    public CapitalRecord getGatheringDataById(long tid) {
        return capitalRecordMapper.selectByPrimaryKey(tid);
    }

    @Override
    public Map<?, ?> checkOrderGathering(ReceivableVO receivableVo) {
        Map map = new HashMap();
        try {
            ReceivableVO receivable = new ReceivableVO();
            CapitalRecord record = capitalRecordMapper.selectByPrimaryKey(receivableVo.getTid());
            //int realPremiumAmt = record.getMustRebateAmt().compareTo(receivableVo.getRealPremiumAmt());
            //int followRpoundage = record.getFollowRpoundage().compareTo(receivableVo.getFollowRpoundage());
            //int replenishRpoundage = record.getReplenishRpoundage().compareTo(receivableVo.getReplenishRpoundage());
            int realPremiumAmt = record.getMustRebateAmt().subtract(receivableVo.getRealPremiumAmt()).abs().compareTo(new BigDecimal("1"));
            int followRpoundage = record.getFollowRpoundage().subtract(receivableVo.getFollowRpoundage()).abs().compareTo(new BigDecimal("1"));
            int replenishRpoundage = record.getReplenishRpoundage().subtract(receivableVo.getReplenishRpoundage()).abs().compareTo(new BigDecimal("1"));
            receivable.setTid(receivableVo.getTid());
            receivable.setRealPremiumAmt(receivableVo.getRealPremiumAmt()); //实收保险公司手续费
            receivable.setRealFollowRpoundage(receivableVo.getFollowRpoundage()); //实收保险公司跟单手续费
            receivable.setRealReplenishRpoundage(receivableVo.getReplenishRpoundage()); //实收保险公司补点手续费
            receivable.setReceiptDate(receivableVo.getReceiptDate());
            receivable.setRemark(receivableVo.getRemark());
            receivable.setUpdateBy(receivableVo.getUpdateBy());
            receivable.setUpdateDate(receivableVo.getUpdateDate());
            if ((realPremiumAmt == -1 || realPremiumAmt == 0) && (followRpoundage == -1 || followRpoundage == 0) && (replenishRpoundage == -1 || replenishRpoundage == 0)) {
                receivable.setStatus(String.valueOf(CapitalRecordStatusEnum.SETTLED.getValue()));
            } else {
                receivable.setStatus(String.valueOf(CapitalRecordStatusEnum.TO_AUDIT.getValue()));
            }
            int num = receivableMapper.updateForOrderCheck(receivable);
            if (num > 0) {
                batchInsertBusinessLog(receivable.getTid(), BusinessLog.ContentEnum.RECEIVABLE_GATHERING.getValue(), receivableVo.getRemark());
            }
            map.put("status", "0001");
            map.put("message","订单收款成功");
        } catch (Exception e) {
            System.out.println(e);
            map.put("status", "0002");
            map.put("message","订单收款失败");
        }
        return map;
    }

    @Override
    public XSSFWorkbook getXlsxWorkbook(String[] headers, List<ReportDataVO> dataset, String fileName) {
        // 声明一个工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 生成一个表格
        XSSFSheet sheet = workbook.createSheet(fileName);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 10);
        // 产生表格标题行
        XSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {
            XSSFCell cell = row.createCell(i);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        try {
            int index = 0;
            // 遍历集合数据，产生数据行
            for (int i = 0; i < dataset.size(); i++) {
                index++;
                row = sheet.createRow(index);
                ReportDataVO t = dataset.get(i);
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
                Field[] fields = t.getClass().getDeclaredFields();
                for (short j = 0; j < headers.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    Field field = fields[j];
                    String fieldName = field.getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    // 判断值的类型后进行强制类型转换
                    String textValue = null;
                    // 其它数据类型都当作字符串简单处理
                    if (value != null && value != "") {
                        textValue = value.toString();
                    }
                    if (textValue != null) {
                        XSSFRichTextString richString = new XSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    @Override
    public List<ReportDataVO> queryReportDataList(ReceivableVO receivable, String reportType) {
        List<ReportDataVO> reportDataList = new ArrayList<ReportDataVO>();
        if ("company".equals(reportType)) {
            reportDataList = receivableMapper.queryReportDataList(receivable);
        } else if ("channel".equals(reportType)) {
            reportDataList = receivableMapper.queryReportChannelDataList(receivable);
        }
        for (int i = 0; i < reportDataList.size(); i++) {
            ReportDataVO rdv = reportDataList.get(i);
            rdv.setNum(String.valueOf(i+1));
            if ("company".equals(reportType)) {
                rdv.setActualPreminmDate(rdv.getPayDate());
            }
            if (StringUtil.checkNotEmpty(rdv.getChannel().getCompanyRegisterName())) {
                rdv.setChannelName(rdv.getChannel().getCompanyRegisterName());
            } else {
                rdv.setChannelName(rdv.getChannel().getContact().getName());
            }
            if ("1".equals(rdv.getPaySubject())) {
                rdv.setPaySubject("环宇康泰");
            } else if ("2".equals(rdv.getPaySubject())) {
                rdv.setPaySubject("青岛盛达");
            } else if ("3".equals(rdv.getPaySubject())) {
                rdv.setPaySubject("玉环天云");
            } else if ("4".equals(rdv.getPaySubject())) {
                rdv.setPaySubject("汉龙");
            }
            if ("0".equals(rdv.getInsuranceType())) {
                rdv.setInsuranceType("交强险");
            } else if ("1".equals(rdv.getInsuranceType())) {
                rdv.setInsuranceType("商业险");
            }
            if ("0".equals(rdv.getCheckResult()) || !StringUtil.checkNotEmpty(rdv.getCheckResult())) {
                rdv.setCheckResult("未核对");
            } else if ("1".equals(rdv.getCheckResult())) {
                rdv.setCheckResult("一致");
            } else if ("2".equals(rdv.getCheckResult())) {
                rdv.setCheckResult("不一致");
            }
        }
        return reportDataList;
    }

    @Override
    public PageList<ReceivableVO> queryChannelTableData(PageVo vo) {
        PageList<ReceivableVO> recordList = receivableMapper.queryChannelTableData(vo, vo.getPageable());
        return recordList;
    }


    @Override
    public List<CheckDataVO> queryCheckDataList() {
        return receivableMapper.queryCheckDataList();
    }

    @Override
    public Map<?, ?> checkOrderSettlement(ReceivableVO receivableVo) {
        Map map = new HashMap();
        try {
            ReceivableVO receivable = new ReceivableVO();
            CapitalRecord record = capitalRecordMapper.selectByPrimaryKey(receivableVo.getTid());
            //int realPremium = record.getRealPremium().compareTo(receivableVo.getRealPremium());
            //实收渠道保费计算误差值是否为1元（含）
            int errorValue = record.getMustPremium().subtract(receivableVo.getRealPremium()).abs().compareTo(new BigDecimal("1"));
            receivable.setTid(receivableVo.getTid());
            receivable.setRealPremium(receivableVo.getRealPremium());
            receivable.setReceiptDate(receivableVo.getReceiptDate());
            receivable.setCapitalSourcr(receivableVo.getCapitalSourcr());
            receivable.setRemark(receivableVo.getRemark());
            if (errorValue == 0 || errorValue == -1) {
                receivable.setStatus(String.valueOf(CapitalRecordStatusEnum.SETTLED.getValue()));
                if (receivableVo.getCapitalSourcr() == 1) {
                    creditTotalService.operateCreditQuota(receivableVo.getChannelId(), CreditRecord.TypeEnum.INCREASE, record.getMustPremium(), record.getPayDate());
                } else if (receivableVo.getCapitalSourcr() == 2) {
                    creditTotalService.operateDeposit(receivableVo.getChannelId(), DepositRecord.TypeEnum.SETTLEMENT, record.getRealPremium(), receivableVo.getRemark());
                    creditTotalService.operateCreditQuota(receivableVo.getChannelId(), CreditRecord.TypeEnum.INCREASE, record.getMustPremium(), record.getPayDate());
                }
            } else {
                receivable.setStatus(String.valueOf(CapitalRecordStatusEnum.TO_AUDIT.getValue()));
            }
            int num = receivableMapper.updateForOrderCheck(receivable);
            if (num > 0) {
                Order order = new Order();
                order.setTid(receivable.getOrdId());
                order.setStatus(11);
                //更新订单状态为已结算
                orderMapper.updateOrderSelectiveById(order);
                batchInsertBusinessLog(receivableVo.getTid(), BusinessLog.ContentEnum.RECEIVABLE_SETTLEMENT.getValue(), receivableVo.getRemark());
            }
            map.put("status", "0001");
            map.put("message","渠道应收结算成功");
        } catch (Exception e) {
            System.out.println(e);
            map.put("status", "0002");
            map.put("message","渠道应收结算失败");
        }
        return map;
    }

    @Override
    public CapitalRecord getChannelCapitalRecordByOrderId(long ordId) {
        CapitalRecord record = receivableMapper.getChannelCapitalRecordByOrderId(ordId);
        return record;
    }

    @Override
    public ReceivableVO getReceivableForSurrender(long tid) {
        return receivableMapper.getReceivableForSurrender(tid);
    }

    @Override
    public Map<?, ?> orderSurrender(CapitalRecord record, ReceivableVO receivable) {
        Map map = new HashMap();
        try {
            SysUser user = (SysUser) SecurityUtils.getSubject().getPrincipal();
            record.setTid(null);
            record.setRealPremium(null);
            record.setRealRebateAmt(null);
            record.setMustPremium(null);
            record.setOrdId(receivable.getOrdId());
            if (receivable.getMustPremium() != null) {
                record.setMustPremium(receivable.getMustPremium());
            }
            record.setMustRebateAmt(receivable.getMustPremiumAmt());
            record.setPayInsurerAmt(receivable.getPayInsurerAmt());
            record.setCreateBy(user.getTid());
            record.setCreateDate(new Date());
            record.setTradeType(2);
            record.setUpdateBy(user.getTid());
            record.setUpdateDate(new Date());
            record.setPayDate(null);
            record.setRemark(receivable.getRemark());
            record.setStatus(CapitalRecordStatusEnum.TO_CHECK.getValue());
            //插入退保订单
            int num = capitalRecordMapper.insertSelective(record);
            //更新订单状态
            if (num > 0) {
                Order order = new Order();
                order.setTid(receivable.getOrdId());
                order.setSurrenderDate(LocalDate.parse(receivable.getSurrenderDateStr()));
                order.setStatus(OrderStatusEnum.TO_RETREAT.getValue());
                order.setChannelStatus(MainOrderStatusEnum.TO_SURRENDER_BALANCE.getIndex());
                //更新订单状态为待退保结算
                orderMapper.updateOrderSelectiveById(order);
                batchInsertBusinessLog(receivable.getTid(), BusinessLog.ContentEnum.RECEIVABLE_SURRENDER.getValue(), receivable.getRemark());
            }
            map.put("status", "0001");
            map.put("message","生成退保订单成功");
        } catch (Exception e) {
            System.out.println(e);
            map.put("status", "0002");
            map.put("message","生成退保订单失败");
        }
        return map;
    }

    /**
     * 校验导入模板是否正确
     * @param xssfWorkbook
     */
    private static boolean checkImportTemplet(XSSFWorkbook xssfWorkbook) {
        String titles[] = {"序号", "出单主体", "保险公司", "投保人", "被保险人", "车牌号", "起保日期", "险种", "保单号", "实收保费", "保费收取时间", "签单保费", "跟单比例", "跟单手续费", "补点比例", "补点手续费"};
        boolean result = true;
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        if (xssfSheet == null) {
            result = false;
        } else {
            XSSFRow xssfRow = xssfSheet.getRow(1);
            for (int i = 0; i < titles.length; i++) {
                String cellValue = getValueXlsx(xssfRow.getCell(i + 1));
                if (!titles[i].equals(cellValue)) {
                    result = false;
                    break;
                }
            }
        }
        return result;
    }

    @Override
    public void updateOverdueStatus(ReceivableVO receivable) {
        receivableMapper.updateOverdueStatus(receivable);
    }

    @Override
    public List<ReceivableVO> queryOrderCapitalRecord(OrderQueryConditionVo order) {
        return receivableMapper.queryOrderCapitalRecord(order);
    }

    /**
     * 批量插入订单操作日志
     */
    public void batchInsertBusinessLog(List<CheckDataVO> doList, int businessType, String businessContent, String remark) {
        List<BusinessLog> logList = new ArrayList<BusinessLog>();
        try {
            SysUser user = (SysUser) SecurityUtils.getSubject().getPrincipal();
            for (int i = 0; i < doList.size(); i ++) {
                CapitalRecord record = capitalRecordMapper.selectByPrimaryKey(doList.get(i).getCapitalId());
                BusinessLog busLog = new BusinessLog();
                busLog.setBusinessType(businessType);
                busLog.setBusinessId(record.getOrdId());
                busLog.setContent(businessContent);
                busLog.setRemark(remark);
                busLog.setCreateBy(user.getTid());
                busLog.setCreateAt(LocalDateTime.now());
                logList.add(busLog);
            }
            doBatchSql(logList, "insert", "batchInsertBusinessLog");
        } catch (Exception e) {
            System.out.println(e);
        }
    }
    public void batchInsertBusinessLog(long businessId, String businessContent, String remark) {
        List<BusinessLog> logList = new ArrayList<BusinessLog>();
        try {
            SysUser user = (SysUser) SecurityUtils.getSubject().getPrincipal();
            CapitalRecord record = capitalRecordMapper.selectByPrimaryKey(businessId);
            BusinessLog busLog = new BusinessLog();
            busLog.setBusinessType(BusinessLog.BusinessTypeEnum.ORDER.getValue());
            busLog.setBusinessId(record.getOrdId());
            busLog.setContent(businessContent);
            busLog.setRemark(remark);
            busLog.setCreateBy(user.getTid());
            busLog.setCreateAt(LocalDateTime.now());
            logList.add(busLog);
            doBatchSql(logList, "insert", "batchInsertBusinessLog");
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    public static String bytesToHexString(byte[] src){
        StringBuilder strBuilder = new StringBuilder();
        if (src == null || src.length <= 0) {
            return null;
        }
        for (int i = 0; i < src.length; i++) {
            int v = src[i] & 0xFF;
            String strhs = Integer.toHexString(v);
            if (strhs.length() < 2) {
                strBuilder.append(0);
            }
            strBuilder.append(strhs);
        }
        return strBuilder.toString();
    }

    /**
     * 根据文件流读取图片文件真实类型
     * @param is
     * @return
     */
    @Override
    public String getTypeByStream(InputStream is) {
        byte[] b = new byte[4];
        try {
            is.read(b, 0, b.length);
        } catch (IOException e) {
            e.printStackTrace();
        }
        String type = bytesToHexString(b).toUpperCase();
        if (type.contains("504B0304")) {
            return "xlsx";
        }
        return type;
    }

}
